ALTER PROC SP_TRANSFER_QUOTATION_LINEITEM
(
	@SALESORDER_ID 		INT,
	@QUOTATION_ID 		INT, 
	@QUOTE_LINEITEM_ID 	INT, 
	@TRANSFER_QUANTITY 	INT,
	@IS_TAXABLE 		INT, 	
	@USER_ID 		VARCHAR(10)
)
AS
BEGIN
	DECLARE @SO_LINEITEM_ID 	INT
	DECLARE @OLD_CHANGE_NUMBER 	INT
	DECLARE @LINEITEM_CHANGE_NO 	INT
	DECLARE @DB_QTY 		INT
	DECLARE @DB_IS_TAXABLE 		INT
	DECLARE @IS_LINEITEM_DETAILS_MODIFIED BIT
	
	SELECT @SO_LINEITEM_ID = LINEITEM_ID FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE SALESORDER_ID = @SALESORDER_ID AND QUOTATION_LINEITEM_ID = @QUOTE_LINEITEM_ID

	SELECT @DB_QTY = QLI_QTY, @DB_IS_TAXABLE = ISNULL( QLI_TAXABLE, -1 ) FROM QUOTATION_ORDER_LINEITEM WHERE QUOTATION_ID = @QUOTATION_ID AND LINEITEM_ID = @QUOTE_LINEITEM_ID

	SET @IS_LINEITEM_DETAILS_MODIFIED = 1
	IF( ( @DB_QTY <> @TRANSFER_QUANTITY ) OR ( @DB_IS_TAXABLE <> @IS_TAXABLE ) )
  	BEGIN
    		SET @IS_LINEITEM_DETAILS_MODIFIED = 0
  	END

	--FOR LAGECY ORDERS	
 	SELECT @OLD_CHANGE_NUMBER = CHANGE_NUMBER FROM QUOTATION_CHANGE_ORDER_LINEITEM WHERE QUOTATION_ID = @Quotation_ID AND LINEITEM_ID = @QUOTE_LINEITEM_ID
 	IF( @OLD_CHANGE_NUMBER IS NULL )
 	BEGIN
		INSERT QUOTATION_CHANGE_ORDER_LINEITEM ( LINEITEM_ID, QUOTATION_ID, CHANGE_NUMBER, LINE_ITEM_NO, PRODUCT_ID, QTY, UNIT_COST, AMOUNT, REQUESTED_DATE, BOOKING_DATE, STATUS, CHANGE_BY_ID, CHANGE_BY_NAME, TAXABLE, PO_LINE_ITEM_NO, TIMESTAMP, TRANSFER, IS_APPROVED, QUOTED_QTY, QUOTED_DATE, LEAD_TIME, QLI_ACTUAL_QTY )
		( SELECT LINEITEM_ID, QUOTATION_ID, 0, QLI_LINE_ITEM_NO, PRODUCT_ID, QLI_QTY, QLI_UNIT_COST, QLI_AMOUNT, QLI_REQUESTED_DATE, QLI_BOOKING_DATE, QLI_STATUS, QLI_ADDED_BY_ID, QLI_ADDED_BY_NAME, QLI_TAXABLE , QLI_PO_LINE_ITEM_NO, TIMESTAMP, TRANSFER, IS_APPROVED, QLI_QUOTED_QTY, QLI_QUOTED_DATE, QLI_LEAD_TIME, QLI_ACTUAL_QTY FROM QUOTATION_ORDER_LINEITEM WHERE QUOTATION_ID = @QUOTATION_ID AND LINEITEM_ID = @QUOTE_LINEITEM_ID )
 	END

	SELECT @LINEITEM_CHANGE_NO = ISNULL( MAX( CHANGE_NUMBER ), 0 ) FROM QUOTATION_CHANGE_ORDER_LINEITEM WHERE QUOTATION_ID = @QUOTATION_ID AND LINEITEM_ID = @QUOTE_LINEITEM_ID
	SET @LINEITEM_CHANGE_NO = @LINEITEM_CHANGE_NO + 1
	
	--Create new record in quotation_change_order_lineitem table when qty or taxable are modified 
	IF( @IS_LINEITEM_DETAILS_MODIFIED = 0 )
	BEGIN
		INSERT QUOTATION_CHANGE_ORDER_LINEITEM 
		( LINEITEM_ID, QUOTATION_ID, CHANGE_NUMBER, LINE_ITEM_NO, PRODUCT_ID, QTY, UNIT_COST, AMOUNT, REQUESTED_DATE, BOOKING_DATE, 
		STATUS, CHANGE_BY_ID, CHANGE_BY_NAME, TAXABLE, PO_LINE_ITEM_NO, TIMESTAMP, TRANSFER, IS_APPROVED, QUOTED_QTY, QUOTED_DATE, LEAD_TIME, QLI_ACTUAL_QTY )
		( SELECT LINEITEM_ID, QUOTATION_ID, @LINEITEM_CHANGE_NO, QLI_LINE_ITEM_NO, PRODUCT_ID,  ( QLI_QTY - @TRANSFER_QUANTITY ), QLI_UNIT_COST, ( @TRANSFER_QUANTITY * QLI_UNIT_COST ), QLI_REQUESTED_DATE, QLI_BOOKING_DATE, 
		QLI_STATUS, @USER_ID, @USER_ID, @IS_TAXABLE, QLI_PO_LINE_ITEM_NO, CURRENT_TIMESTAMP, TRANSFER, IS_APPROVED, QLI_QUOTED_QTY, QLI_QUOTED_DATE, QLI_LEAD_TIME, QLI_ACTUAL_QTY FROM QUOTATION_ORDER_LINEITEM WHERE QUOTATION_ID = @QUOTATION_ID AND LINEITEM_ID = @QUOTE_LINEITEM_ID )	
	END	

	SELECT @LINEITEM_CHANGE_NO = ISNULL( MAX( CHANGE_NUMBER ), 0 ) FROM QUOTATION_CHANGE_ORDER_LINEITEM WHERE QUOTATION_ID = @QUOTATION_ID AND LINEITEM_ID = @QUOTE_LINEITEM_ID
	--SET @LINEITEM_CHANGE_NO = @LINEITEM_CHANGE_NO + 1

	--Create new record in quotation_change_order_lineitem table when quote lineitem is transfer to sales order
	INSERT QUOTATION_CHANGE_ORDER_LINEITEM ( LINEITEM_ID, QUOTATION_ID, LINE_ITEM_NO, PRODUCT_ID, CHANGE_NUMBER, 
		QTY, UNIT_COST, AMOUNT, REQUESTED_DATE, BOOKING_DATE, STATUS, CHANGE_BY_ID, CHANGE_BY_NAME, TAXABLE, 
		PO_LINE_ITEM_NO, TIMESTAMP, TRANSFER, IS_APPROVED, QUOTED_QTY, QUOTED_DATE, SO_LINEITEM_ID, LEAD_TIME, QLI_ACTUAL_QTY )
	( SELECT LINEITEM_ID, QUOTATION_ID, QLI_LINE_ITEM_NO, PRODUCT_ID, @LINEITEM_CHANGE_NO, 
		@TRANSFER_QUANTITY, QLI_UNIT_COST, ( @TRANSFER_QUANTITY * QLI_UNIT_COST ), 
		QLI_REQUESTED_DATE, QLI_BOOKING_DATE, QLI_STATUS, @USER_ID, @USER_ID, QLI_TAXABLE, 
		QLI_PO_LINE_ITEM_NO, CURRENT_TIMESTAMP, TRANSFER, IS_APPROVED, QLI_QUOTED_QTY, QLI_QUOTED_DATE, @SO_LINEITEM_ID, QLI_LEAD_TIME, QLI_ACTUAL_QTY 
		FROM QUOTATION_ORDER_LINEITEM WHERE QUOTATION_ID = @QUOTATION_ID AND LINEITEM_ID = @QUOTE_LINEITEM_ID )	
	
	
	UPDATE QUOTATION_ORDER_LINEITEM SET 
		QLI_QTY 	= ( QLI_QTY - @TRANSFER_QUANTITY ), 
		QLI_AMOUNT 	= ( ( QLI_QTY - @TRANSFER_QUANTITY ) * QLI_UNIT_COST ), 
		QLI_TAXABLE 	= @IS_TAXABLE
	WHERE LINEITEM_ID = @QUOTE_LINEITEM_ID
END
